﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using SARS.DAO.Facades;
using SARS.Entity;
using SARS.Entity.Factory;

namespace SARS.DAO
{
    public class StudentAbsenceReasonDAO : IStudentAbsenceReasonDAO
    {
        private readonly SqlConnection _conn = new SqlConnection(Utils.SarConnectionString);
        private SqlCommand _cmd;
        private SqlDataReader _dr;

        public int InsertStudentAbsenceReason(StudentAbsenceReasonEntity entity)
        {
            int result = 0;

            try
            {
                var id = new SqlParameter("id", 0) {Direction = ParameterDirection.Output};

                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "INSERT INTO [StudentAbsenceReason]([AttendanceSessionID],[StudentAbsenceReason],[IsApproved]" +
                        ",[Creator],[CreatedDate]) " +
                        "VALUES (@attendanceSessionID,@studentAbsenceReason,@isApproved" +
                        ",@creator,@createdDate);" +
                        " SELECT @id=SCOPE_IDENTITY()", _conn);

                _cmd.Parameters.AddWithValue("@attendanceSessionID", entity.AttendanceSessionId);
                _cmd.Parameters.AddWithValue("@studentAbsenceReason", entity.StudentAbsenceReason);
                _cmd.Parameters.AddWithValue("@isApproved", entity.IsApproved);
                _cmd.Parameters.AddWithValue("@creator", entity.Creator);
                _cmd.Parameters.AddWithValue("@createdDate", entity.CreatedDate);

                _cmd.Parameters.Add(id);
                _cmd.ExecuteNonQuery();

                result = Convert.ToInt32(id.Value);

                // Cleanup command and connection objects.
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return result;
        }

        public int UpdateStudentAbsenceReason(StudentAbsenceReasonEntity absence)
        {
            int effRow = 0;
            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "UPDATE [StudentAbsenceReason] SET [StudentAbsenceReason] = @reason, [ModifiedDate] = GETDATE() ,[ModifiedBy] = @tutorId ,[IsApproved] = @isApprove" +
                        " WHERE [AttendanceSessionID] = @attendId", _conn);
                _cmd.Parameters.AddWithValue("@tutorId", absence.ModifiedBy);
                _cmd.Parameters.AddWithValue("@reason", absence.StudentAbsenceReason);
                _cmd.Parameters.AddWithValue("@isApprove", absence.IsApproved);
                _cmd.Parameters.AddWithValue("@attendId", absence.AttendanceSessionId);

                effRow = _cmd.ExecuteNonQuery();

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[StudentAbsenceReasonDAO] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }
            return effRow;
        }

        public StudentAbsenceReasonEntity GetStudentAbsenceReasonById(int absentId)
        {
            StudentAbsenceReasonEntity absentReason = StudentAbsenceReasonFactory.Create();

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "SELECT * FROM [dbo].[StudentAbsenceReason] WHERE [AttendanceSessionID] = @absentId", _conn);
                _cmd.Parameters.AddWithValue("@absentId", absentId);

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                while (_dr.HasRows && _dr.Read())
                {
                    absentReason.AttendanceSessionId = Int32.Parse(_dr["AttendanceSessionID"].ToString());
                    absentReason.StudentAbsenceReason = _dr["StudentAbsenceReason"].ToString();
                    absentReason.IsApproved = Int32.Parse(_dr["IsApproved"].ToString());
                    absentReason.Creator = Int32.Parse(_dr["Creator"].ToString());
                    absentReason.CreatedDate = DateTime.Parse(_dr["CreatedDate"].ToString());
                    string modDate = _dr["ModifiedDate"].ToString();
                    if (!string.IsNullOrEmpty(modDate))
                    {
                        absentReason.ModifiedDate = DateTime.Parse(modDate);
                    }
                    string mod = _dr["ModifiedBy"].ToString();
                    if (!string.IsNullOrEmpty(mod))
                    {
                        absentReason.ModifiedBy = Int32.Parse(mod);
                    }
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return absentReason;
        }

        public List<StudentAbsenceReasonEntity> GetAllStudentAbsenceReasonByTutorId(int tutorId)
        {
            var absenceReasonList = new List<StudentAbsenceReasonEntity>();

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "Select [AttendanceSessionID], [StudentAbsenceReason], A.[Creator], A.[CreatedDate]," +
                        "[IsApproved],A.[ModifiedDate],A.[ModifiedBy], B.[SessionID], B.[StudentID] from [StudentAbsenceReason] A INNER JOIN [AttendanceSession] B " +
                        "ON A.AttendanceSessionID = B.ID INNER JOIN [Sessions] C ON B.SessionID = C.SessionID " +
                        "WHERE B.[StudentID] IN (SELECT DISTINCT SG.StudentID FROM [StudentGroup] SG INNER JOIN [GroupManager] GM ON SG.GroupID = GM.GroupID WHERE GM.ManagerID = @tutorId)", _conn);
                _cmd.Parameters.Add(new SqlParameter("@tutorId", tutorId));

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    StudentAbsenceReasonEntity absentReason = StudentAbsenceReasonFactory.Create();
                    absentReason.AttendanceSessionId = Int32.Parse(_dr["AttendanceSessionID"].ToString());
                    absentReason.StudentAbsenceReason = _dr["StudentAbsenceReason"].ToString();
                    absentReason.Creator = Int32.Parse(_dr["Creator"].ToString());
                    absentReason.CreatedDate = Convert.ToDateTime(_dr["CreatedDate"].ToString());
                    absentReason.IsApproved = Int32.Parse(_dr["IsApproved"].ToString());

                    string modifyDate = _dr["ModifiedDate"].ToString();
                    if (!string.IsNullOrEmpty(modifyDate))
                    {
                        absentReason.ModifiedDate = DateTime.Parse(modifyDate);
                    }
                    string modifyTutor = _dr["ModifiedBy"].ToString();
                    if (!string.IsNullOrEmpty(modifyTutor))
                    {
                        absentReason.ModifiedBy = Int32.Parse(modifyTutor);
                    }

                    absentReason.AttendanceSession = new AttendanceSessionEntity
                    {
                        SessionID = Int32.Parse(_dr["SessionID"].ToString()),
                        StudentID = Int32.Parse(_dr["StudentID"].ToString())
                    };

                    absenceReasonList.Add(absentReason);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Session] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return absenceReasonList;
        }
    }
}